At St Mary’s Shaftesbury, one of the many additional functions we have built, to work alongside our Schoolbase MIS (Management Information System), is the automation of a weekly ‘Rewards and Sanctions’ report.

Despite this being one of the more simpler technical features we have implemented, it remains one of the most popular; indeed, if this weekly report fails to magically appear in appropriate email in-boxes on time, we are informed by concerned parties, quicker than you can say ‘where’s my rewards and sanctions report!

how the magic works!

These reports are written in Crystal Reports, and scheduled for weekly email delivery, using an excellent and free (for up to 6 reports), software application called Crystal Delivery.

(please note: we have historically used version 8.4.0 of Crystal Delivery, which was completely free with no report limitation.  It appears that this version is no longer available on their website; a newer, and possibly better version 10.4.2, is now the preferred download – this is still free to use, for up to 6 reports)

Whilst nowadays, we tend to prefer a more web centric approach for data delivery (embedding directly into our Firefly VLE etc), Crystal still has a significant role to play, and is for the best part, an excellent business intelligence and data reporting tool; indeed at the time of posting, Schoolbase as a product, now uses Crystal as it’s main report generation system.

It’s worth mentioning, that we have been using Crystal Reports to access a wealth of data, since we started using Schoolbase nearly ten years ago.

installation

1. Download and install Crystal Delivery from the main Groff website here - (selecting either the free 6 report version or the fully purchased option).
2. You may also need to download and install Microsoft SQL Compact 3.5 from here.
3. Again, depending on your environment, you may need to download and install the Crystal Reports Runtime from here.

I did experience a few minor issues when testing out the latest version, and I would strongly recommend you install all the above prerequisites prior to adding any reports.

Once you have installed Crystal Delivery, create an SQL View on your Schoolbase database, so that Crystal Reports can access appropriate data, specific to rewards and sanctions.

This view can be created, by running the below script on your Schoolbase database:

CREATE VIEW DBA_RandSView
AS
SELECT TOP (100) PERCENT Sanctions.Sanction
	,Pupil.Pu_Surname
	,Pupil.Pu_GivenName
	,Years.YearDesc
	,PupSanction.PupSanDate
	,PupSanction.PupSanCount
	,PupSanction.PupNewNote
	,Staff.UserName
	,Sanctions.SanIdent
	,Pupil.PupOrigNum
	,PupSanction.PupSanNote
FROM dbo.PupSanction AS PupSanction
INNER JOIN dbo.Staff AS Staff ON PupSanction.UserIdent = Staff.UserIdent
INNER JOIN dbo.Pupil AS Pupil ON PupSanction.PupOrigNum = Pupil.PupOrigNum
INNER JOIN dbo.Sanctions AS Sanctions ON PupSanction.SanIdent = Sanctions.SanIdent
INNER JOIN dbo.Years AS Years ON Pupil.YearIdent = Years.YearIdent
ORDER BY Years.YearDesc
	,PupSanction.PupSanDate

Finally, you can download a copy of the actual crystal report, that will be used to generate the rewards and sanctions export, from my GitHub account here – save this to a central location (e.g. a ‘schedules’ folder on your server).

A note on data location

Please be advised that you will need a copy of Crystal Reports in order to change the Datasource Location.

Open the report in Crystal, and change the data-source location (using these instructions) connecting to your own Schoolbase database.

If you prefer, you could create an ODBC connection titled Schoolbase, which this report should recognise (no Crystal Reports required).

configuration

Start the Crystal Delivery application, and configure the email settings (Tools > Mail Settings):

Enter your SMTP server details etc:

Create a new Schedule, by clicking the ‘Schedule’ button, and locate the Crystal Report (titled ‘R&SReport.rpt’) that you downloaded previously:

Locate an export directory and create a default export file name, by clicking on the ‘Export Directory’ tab, ensuring you create this as a pdf type document (this can just be a local folder on the server):

Ensure you have the ‘Email File’ check box ticked:

You then need to provide appropriate filters for the query using the ‘Parameters’ tab.  In our instance, we set the two query parameters as below:

Date From:  Today (Days):-7
Date To: Today (Days):0

This will provide a data export, of rewards and sanctions for only the past week:

Compose the email body content on the ‘Export Email’ tab:

Finally, set the scheduling requirements on the ‘Schedule’ tab; in this example, we have this scheduling at 4AM, only on a Monday:

…and that’s it!

You may of course wish to edit the Crystal Report to suite your own requirements – indeed I would encourage this.  We have different exports that target specific groups (House Mistresses, Head’s of School etc) – all governed by different query filters.

We also have colour format changes based on ‘Reward’ or ‘Sanction’ etc.

Final note

I should mention, that we have historically run these schedules on Crystal Delivery version 8.4.0 (which we still have) and in truth, whilst testing out version 10.4.2, I did experience some ‘flaky’ behaviour.  Equally, to be fair, 10.4.2 is currently in a beta release; as such we will probably stick with 8.4.0 for the time being!